from datetime import datetime
from typing import Literal

from pydantic import BaseModel, field_validator, EmailStr, IPvAnyAddress
from sqlalchemy import Column, Integer, String, DateTime, select, func
from sqlalchemy.orm import declarative_base

from utils.drivers.database.engine import AsyncSession
from utils.enums import MessageStatus, TimeKey, Sort
from utils.exception import AppException, StatusCode

Base = declarative_base()


class MessageModel(BaseModel):
    id: int
    username: str
    tel: str
    email: str
    ip: str
    content: str
    status: MessageStatus
    create_time: datetime = None
    update_time: datetime = None


class Message(Base):
    __tablename__ = "message"

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(255))
    tel = Column(String(255))
    email = Column(String(255))
    ip = Column(String(255))
    content = Column(String)
    status = Column(String(255))
    create_time = Column(DateTime, default=datetime.now())
    update_time = Column(DateTime, default=datetime.now(), onupdate=datetime.now())

    def model(self):
        return MessageModel(
            id=self.id,  # type: ignore
            username=self.username,  # type: ignore
            tel=self.tel,  # type: ignore
            email=self.email,  # type: ignore
            ip=self.ip,  # type: ignore
            content=self.content,  # type: ignore
            status=MessageStatus(self.status),
            create_time=self.create_time,  # type: ignore
            update_time=self.update_time  # type: ignore
        )

    @classmethod
    async def get(cls, id_: int = None, username: str = None, tel: str = None, email: str = None, ip: str = None,
            content: str = None, status: MessageStatus = None,
            time: tuple[datetime | None, datetime | None, TimeKey] = None,
            sort: tuple[Literal["id", TimeKey.CreateTime, TimeKey.UpdateTime], Sort] = (TimeKey.CreateTime, Sort.Desc),
            page: int = 1, page_size: int = 12) -> tuple[list[MessageModel], int]:
        stmt = select(cls)
        if id_:
            stmt = stmt.where(cls.id == id_)
        if username:
            stmt = stmt.where(cls.username == username)
        if tel:
            stmt = stmt.where(cls.tel == tel)
        if email:
            stmt = stmt.where(cls.email == email)
        if ip:
            stmt = stmt.where(cls.ip == ip)
        if content:
            stmt = stmt.where(cls.content.like(f"%{content}%"))
        if status:
            stmt = stmt.where(cls.status == status.value)
        if time:
            tk = cls.create_time
            if time[2] == TimeKey.UpdateTime:
                tk = cls.update_time
            if time[0]:
                stmt = stmt.where(tk >= time[0])
            if time[1]:
                stmt = stmt.where(tk <= time[1])
        if sort:
            key = cls.id
            if sort[0] == "id":
                key = cls.id
            elif sort[0] == TimeKey.CreateTime:
                key = cls.create_time
            elif sort[0] == TimeKey.UpdateTime:
                key = cls.update_time
            order_by = key.asc()
            if sort[1] == Sort.Desc:
                order_by = key.desc()
            elif sort[1] == Sort.Asc:
                order_by = key.asc()
            stmt = stmt.order_by(order_by)
        if page and page_size:
            stmt = stmt.limit(page_size).offset((page - 1) * page_size)

        try:
            async with AsyncSession() as session:
                async with session.begin():
                    count_stmt = select(func.count()).select_from(stmt.alias("subquery"))
                    count = await session.execute(count_stmt)
                    count = count.scalar()

                    messages = await session.execute(stmt)
                    messages = messages.scalars().all()
                    if not messages:
                        messages = []
        except Exception as e:
            raise AppException(StatusCode.QueryMessageError, detail=str(e))

        return messages, count

    async def save(self) -> bool:
        try:
            async with AsyncSession() as session:
                async with session.begin():
                    session.add(self)
        except Exception as e:
            raise AppException(StatusCode.AddMessageError, detail=str(e))

        return True




